--06-05-2008 - UPDATE INVOICE NUMBER AND DATE FOR PURCHASE ORDERS (table PurJournalUserOrderHeader)
--TrackIt #8343 and #8365
--The invoice data was not transfered from Insight V1 in May 2008 and this script fixes the problem.
----------------------------------------------------------------------------------------------
--Step 1.
--add 2 new columns to tables PurJournalUserOrderHeader and PurJournalUserOrderHeader
---------
--add 2 columns to table PurPOUserOrderHeader 05-29-2008
--InvoiceNumber
IF NOT EXISTS    (  SELECT COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE (   TABLE_NAME = 'PurPOUserOrderHeader'
            AND COLUMN_NAME = 'InvoiceNumber'  )) 
ALTER TABLE PurPOUserOrderHeader ADD InvoiceNumber varchar(16) NULL
--InvoiceDate
IF NOT EXISTS    (  SELECT COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE (   TABLE_NAME = 'PurPOUserOrderHeader'
            AND COLUMN_NAME = 'InvoiceDate' ))
ALTER TABLE PurPOUserOrderHeader ADD InvoiceDate datetime NULL

--add 2 columns to table PurJournalUserOrderHeader 05-29-2008
--InvoiceNumber
IF NOT EXISTS    (  SELECT COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE (   TABLE_NAME = 'PurJournalUserOrderHeader'
            AND COLUMN_NAME = 'InvoiceNumber'  )) 
ALTER TABLE PurJournalUserOrderHeader ADD InvoiceNumber varchar(16) NULL
--InvoiceDate
IF NOT EXISTS    (  SELECT COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE (   TABLE_NAME = 'PurJournalUserOrderHeader'
            AND COLUMN_NAME = 'InvoiceDate' ))
ALTER TABLE PurJournalUserOrderHeader ADD InvoiceDate datetime NULL

--Step 2.
--Creates tables jeh and jed - Purchase Orders from V1

--step 3. Backup table
--------------------
-- check if backup table exists
IF OBJECT_ID('PurJournalUserOrderHeaderNOINVOICE') IS NOT NULL 
				DROP TABLE PurJournalUserOrderHeaderNOINVOICE

select * into PurJournalUserOrderHeaderNOINVOICE
from PurJournalUserOrderHeader
--------------------
--Step 4.
--Create table jehA - with included OrderAmount
-------------------
IF OBJECT_ID('jehA') IS NULL 
select *
into jehA
from
(
select jeh.nUserOrderId,jeh.sInvoiceNumber,jeh.dtInvoiceDate, jeh.sPSPOID, jeh.sVendorName, jeh.sPSReqID, sum(cAmount) as JournalAmount, count(1) as counter from jeh
inner join jed on jeh.nUserOrderId = jed.nUserOrderId
group by jeh.nUserOrderId, jeh.sInvoiceNumber,jeh.dtInvoiceDate, jeh.sPSPOID, jeh.sVendorName, jeh.sPSReqID
) jehT

--Step 5.
--Update PurJournalUserOrderHeader InvoiceNumber and InvoiceDate
--------------------
declare
		@uId int
		,@rec int
		,@cnt int
		,@nUserOrderId int
		,@sInvoiceNumber varchar(25)
		,@dtInvoiceDate datetime
		,@sPSPOID varchar(10)
		,@sVendorName varchar(25)
		,@sPSReqID varchar(10)
		,@JournalAmount money

select @rec = 0
		,@cnt = 0

	  DECLARE purcursorJE CURSOR FOR
	  SELECT 
		nUserOrderId
		,sInvoiceNumber
		,dtInvoiceDate
		,sPSPOID
		,sVendorName
		,sPSReqID
		,JournalAmount
	    from jehA

	  OPEN purcursorJE
	  FETCH NEXT FROM purcursorJE
		into @nUserOrderId
		,@sInvoiceNumber
		,@dtInvoiceDate
		,@sPSPOID
		,@sVendorName
		,@sPSReqID
		,@JournalAmount

	  WHILE (@@FETCH_STATUS = 0)
	  BEGIN

		select top 1 @uId = PurUserOrderHeaderId
		from PurJournalUserOrderHeader
		where
			JournalAmount = @JournalAmount
			and JournalId = @sPSPOID
			and InvoiceNumber is null
			
			
			if @uId is not null
			begin
		select @cnt = @cnt + 1
print @cnt
print @uId
				update PurJournalUserOrderHeader
					set InvoiceNumber = substring(@sInvoiceNumber,1,16)
						,InvoiceDate = @dtInvoiceDate
				where PurUserOrderHeaderId = @uId
			end

	  FETCH NEXT FROM purcursorJE
		into @nUserOrderId
		,@sInvoiceNumber
		,@dtInvoiceDate
		,@sPSPOID
		,@sVendorName
		,@sPSReqID
		,@JournalAmount

	  END --while

	  CLOSE purcursorJE
	  DEALLOCATE purcursorJE
------------------------------
